﻿/*
* CRM_Customer_wxinfo.cs
*
* 功 能： N/A
* 类 名： CRM_Customer_wxinfo
*
* Ver    变更日期             负责人     变更内容
* ───────────────────────────────────
* V1.0  2016-03-04 20:39:55    黄润伟    
*
* Copyright © 2015 www.xhdcrm.com All rights reserved.
*┌──────────────────────────────────┐
*│　版权所有：小黄豆                      　　　　　　　　　　　　　　│
*└──────────────────────────────────┘
*/
using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using XHD.DBUtility;//Please add references
namespace XHD.DAL
{
    /// <summary>
    /// 数据访问类:CRM_Customer_wxinfo
    /// </summary>
    public partial class CRM_Customer_wxinfo
    {
        public CRM_Customer_wxinfo()
        { }
        #region  BasicMethod


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(XHD.Model.CRM_Customer_wxinfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CRM_Customer_wxinfo(");
            strSql.Append("wxid,head_img,nick_name,remark_name,user_name,create_id,create_time)");
            strSql.Append(" values (");
            strSql.Append("@wxid,@head_img,@nick_name,@remark_name,@user_name,@create_id,@create_time)");
            SqlParameter[] parameters = {
                new SqlParameter( "@wxid", SqlDbType.VarChar,500),
                new SqlParameter( "@head_img", SqlDbType.VarChar,500),
                new SqlParameter( "@nick_name", SqlDbType.VarChar,500),
                new SqlParameter( "@remark_name", SqlDbType.VarChar,500),
                new SqlParameter( "@user_name", SqlDbType.VarChar,500),
                new SqlParameter( "@create_id", SqlDbType.VarChar,500),
                new SqlParameter( "@create_time", SqlDbType.DateTime)
            };
            parameters[0].Value = model.wxid;
            parameters[1].Value = model.head_img;
            parameters[2].Value = model.nick_name;
            parameters[3].Value = model.remark_name;
            parameters[4].Value = model.user_name;
            parameters[5].Value = model.create_id;
            parameters[6].Value = model.create_time;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows > 0 ? true : false;
        }

        /// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(XHD.Model.CRM_Customer_wxinfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CRM_Customer_wxinfo set ");
            strSql.Append("head_img = @head_img,");
            strSql.Append("nick_name = @nick_name,");
            strSql.Append("remark_name = @remark_name,");
            strSql.Append("user_name = @user_name,");
            strSql.Append(" where wxid=@wxid ");
            SqlParameter[] parameters = {
                new SqlParameter( "@head_img", SqlDbType.VarChar,500),
                new SqlParameter( "@nick_name", SqlDbType.VarChar,500),
                new SqlParameter( "@remark_name", SqlDbType.VarChar,500),
                new SqlParameter( "@user_name", SqlDbType.VarChar,500)
            };
            parameters[0].Value = model.head_img;
            parameters[1].Value = model.nick_name;
            parameters[2].Value = model.remark_name;
            parameters[3].Value = model.user_name;
            parameters[4].Value = model.wxid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows > 0 ? true : false;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CRM_Customer_wxinfo ");
            strSql.Append(" where wxid=@wxid ");
            SqlParameter[] parameters = {
                    new SqlParameter("@wxid", SqlDbType.VarChar,50)           };
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows > 0 ? true : false;
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CRM_Customer_wxinfo ");
            strSql.Append(" where wxid in (" + idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows > 0 ? true : false;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,Serialnumber,cus_name,cus_add,cus_tel,cus_fax,cus_email,cus_website,cus_industry_id,Provinces_id,City_id,contactsname, ");
            strSql.Append("cus_type_id,cus_level_id,cus_source_id,DesCripe,Remarks,emp_id,isPrivate,lastfollow,xy,isDelete,Delete_time,a.create_id,a.create_time,");
            strSql.Append("cus_extend,xhs_website,xhs_nickname,xhs_guanzhu,xhs_fun,xhs_collect,xhs_notes,xhs_offer,CONVERT(NVARCHAR(20), xhs_datatime,120) AS xhs_datatime,");
            strSql.Append("xhs_tandian,CONVERT(NVARCHAR(20),xhs_tandiantime,120) AS xhs_tandiantime,xhs_code,xhs_category,xhs_isPartner,wx_username,wxid,");
            strSql.Append("head_img,nick_name,remark_name,user_name ,");
            strSql.Append("(select params_name from Sys_Param where id = a.cus_industry_id) as cus_industry,");
            strSql.Append("(select params_name from Sys_Param where id = a.cus_level_id) as cus_level,");
            strSql.Append("(select params_name from Sys_Param where id = a.cus_type_id) as cus_type,");
            strSql.Append("(select params_name from Sys_Param where id = a.cus_source_id) as cus_source,");
            strSql.Append("(select City from Sys_Param_City where id = a.City_id) as City, ");

            strSql.Append("(select Provinces from Sys_Param_Provinces where id = a.Provinces_id) as Provinces, ");
            strSql.Append("(select dep_name from hr_department where id = (select dep_id from hr_employee where id = a.emp_id)) as department,");
            strSql.Append("(select name from hr_employee where id = a.emp_id) as employee ");

            strSql.Append("FROM CRM_Customer a inner join CRM_Customer_wxinfo b on a.wx_username=b.user_name ");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,Serialnumber,cus_name,cus_add,cus_tel,cus_fax,cus_email,cus_website,cus_industry_id,Provinces_id,City_id,contactsname,cus_type_id,cus_level_id,cus_source_id,DesCripe,Remarks,emp_id,isPrivate,lastfollow,xy,isDelete,Delete_time,create_id,create_time,cus_extend,xhs_website,xhs_nickname,xhs_guanzhu,xhs_fun,xhs_collect,xhs_notes,xhs_offer,CONVERT(NVARCHAR(20),xhs_datatime,120) AS xhs_datatime,xhs_tandian,CONVERT(NVARCHAR(20),xhs_tandiantime,120) AS xhs_tandiantime,xhs_code,xhs_category,xhs_isPartner,wx_username,wxid,head_img,nick_name,remark_name,user_name ");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_industry_id) as cus_industry");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_level_id) as cus_level");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_type_id) as cus_type");
            strSql.Append(",(select params_name from Sys_Param where id = CRM_Customer.cus_source_id) as cus_source");
            strSql.Append(",(select City from Sys_Param_City where id = CRM_Customer.City_id) as City");
            strSql.Append(",(select Provinces from Sys_Param_Provinces where id = CRM_Customer.Provinces_id) as Provinces");
            strSql.Append(",(select dep_name from hr_department where id = (select dep_id from hr_employee where id = CRM_Customer.emp_id)) as department");
            strSql.Append(",(select name from hr_employee where id = CRM_Customer.emp_id) as employee");
            strSql.Append(" FROM CRM_Customer ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize, int PageIndex, string strWhere, string filedOrder, out string Total)
        {
            StringBuilder strSql_inner = new StringBuilder();
            StringBuilder strSql_grid = new StringBuilder();
            StringBuilder strSql_total = new StringBuilder();

            //联合数据
            strSql_inner.Append("( SELECT id, Serialnumber, cus_name, cus_add,ismark,state, cus_tel, cus_email,cus_fax, cus_website, contactsname,cus_industry_id, Provinces_id, City_id, cus_type_id, cus_level_id, cus_source_id, DesCripe, Remarks, emp_id, isPrivate, lastfollow, xy, isDelete, Delete_time, create_id, create_time,cus_extend,xhs_website,xhs_nickname,wxid,head_img,nick_name,remark_name,user_name  ");
            strSql_inner.Append(",cus_industry,cus_level, cus_type,cus_source,City,Provinces,department,employee");
            strSql_inner.Append($",ROW_NUMBER() OVER(Order by {filedOrder}) AS n , ");
            strSql_inner.Append("ISNULL(xhs_guanzhu,0) as xhs_guanzhu,ISNULL(xhs_fun,0) as xhs_fun,ISNULL(xhs_collect,0) as xhs_collect,ISNULL(xhs_notes,0) as xhs_notes,xhs_offer,ISNULL(CONVERT(nvarchar(10), xhs_datatime,120),'') as xhs_datatime,xhs_tandian,ISNULL(CONVERT(nvarchar(10), xhs_tandiantime,120),'') as xhs_tandiantime,ISNULL(xhs_code,'') as xhs_code,  ");
            strSql_inner.Append("ISNULL(xhs_category,'') as xhs_category,xhs_isPartner,ISNULL(wx_username,'') as wx_username FROM ");
            strSql_inner.Append(" (");
            strSql_inner.Append(" SELECT  ");
            strSql_inner.Append(" * ");
            strSql_inner.Append(", (select params_name from Sys_Param where id = w1.cus_industry_id) as cus_industry ");
            strSql_inner.Append(", (select params_name from Sys_Param where id = w1.cus_level_id) as cus_level ");
            strSql_inner.Append(", (select params_name from Sys_Param where id = w1.cus_type_id) as cus_type ");
            strSql_inner.Append(", (select params_name from Sys_Param where id = w1.cus_source_id) as cus_source ");
            strSql_inner.Append(", (select City from Sys_Param_City where id = w1.City_id) as City ");
            strSql_inner.Append(", (select Provinces from Sys_Param_Provinces where id = w1.Provinces_id) as Provinces ");
            strSql_inner.Append(", (select dep_name from hr_department where id = (select dep_id from hr_employee where id = w1.emp_id)) as department ");
            strSql_inner.Append(", (select name from hr_employee where id = w1.emp_id) as employee ");
            strSql_inner.Append("FROM (SELECT * from CRM_Customer UNION SELECT * FROM CRM_Customer where isPrivate = 1) as w1 ");
            strSql_inner.Append(" ) w2 ");
            if (strWhere.Trim() != "")
            {
                strSql_inner.Append(" WHERE " + strWhere);
            }
            strSql_inner.Append(") W3");

            //Total数据
            strSql_total.Append(" SELECT COUNT(ID) FROM ");
            strSql_total.Append(strSql_inner.ToString());

            //grid数据
            strSql_grid.Append(" SELECT * FROM ");
            strSql_grid.Append(strSql_inner.ToString());
            strSql_grid.Append(" WHERE n BETWEEN " + (PageSize * (PageIndex - 1) + 1) + " AND " + PageSize * PageIndex);

            Total = DbHelperSQL.Query(strSql_total.ToString()).Tables[0].Rows[0][0].ToString();
            return DbHelperSQL.Query(strSql_grid.ToString());
        }

        #endregion  BasicMethod

        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

